﻿using System.Data;
using System.Data.SqlClient;
using System.Globalization;
using Pantry.Commons;
using Pantry.Entity;
using System;

namespace Pantry.DataAccess
{
    public class FunderDao
    {
        private readonly SqlConnection _sqlConnection;

        public FunderDao()
        {
            _sqlConnection = SqlConnectionTool.SqlGetConnection();
        }

        public FunderDao(string sqlConnection)
        {
            _sqlConnection = new SqlConnection(sqlConnection);
        }

        #region sql command
        private const string QueryStringInsertFunder = @"
                INSERT INTO Funders(
                FundingSource, ContactID, FundingAmount, FundingNeeds, FundingContinuationNeed, FundingStart, FundingEnd,
                FundingCotinuationAmount, FundingContinuationDetail, FundrasingForText, FundrasingNeeds, FundrasingWhy,
                FundrasingComplete, FundrasingRequireBy, FundrasingDonorAnonymous, FundrasingDonorAmount, 
                FundrasingDonationDate, FundrasingDonationIncremental, FundrasingCompleteDate, ServiceID) 
                VALUES(
                @FundingSource, @ContactID, @FundingAmount, @FundingNeeds, @FundingContinuationNeed, @FundingStart, @FundingEnd,
                @FundingCotinuationAmount, @FundingContinuationDetail, @FundrasingForText, @FundrasingNeeds, @FundrasingWhy,
                @FundrasingComplete, @FundrasingRequireBy, @FundrasingDonorAnonymous, @FundrasingDonorAmount, @FundrasingDonationDate,
                @FundrasingDonationIncremental, @FundrasingCompleteDate, @ServiceID)";
        private const string QueryStringUpdateFunder = @"
                UPDATE Funders SET FundingSource=@FundingSource, ContactID=@ContactID, FundingAmount=@FundingAmount, 
                FundingNeeds=@FundingNeeds, FundingContinuationNeed=@FundingContinuationNeed, FundingStart=@FundingStart, 
                FundingEnd=@FundingEnd, FundingCotinuationAmount=@FundingCotinuationAmount, FundingContinuationDetail=@FundingContinuationDetail,
                FundrasingForText=@FundrasingForText, FundrasingNeeds=@FundrasingNeeds, FundrasingWhy=@FundrasingWhy,
                FundrasingComplete=@FundrasingComplete, FundrasingRequireBy=@FundrasingRequireBy, 
                FundrasingDonorAnonymous=@FundrasingDonorAnonymous, FundrasingDonorAmount=@FundrasingDonorAmount, 
                FundrasingDonationDate=@FundrasingDonationDate, FundrasingDonationIncremental=@FundrasingDonationIncremental, 
                FundrasingCompleteDate=@FundrasingCompleteDate WHERE ServiceID=@ServiceId";
        private const string QueryStringSelectFunderById = @"
                SELECT FundingSource, ContactID, FundingAmount, FundingNeeds, FundingContinuationNeed, convert(varchar(10),FundingStart,101) as FundingStart, convert(varchar(10),FundingEnd,101) as FundingEnd,
                FundingCotinuationAmount, FundingContinuationDetail, FundrasingForText, FundrasingNeeds, FundrasingWhy,
                FundrasingComplete, convert(varchar(10),FundrasingRequireBy,101) as FundrasingRequireBy, FundrasingDonorAnonymous, FundrasingDonorAmount, 
                convert(varchar(10),FundrasingDonationDate,101) as FundrasingDonationDate, FundrasingDonationIncremental, convert(varchar(10),FundrasingCompleteDate,101) as FundrasingCompleteDate, ServiceID FROM Funders
                WHERE ServiceID=@id";
        private const string QueryStringGetMaxId = @"
                SELECT TOP 1 ID FROM Services ORDER BY ID DESC";
        private const string QueryStringSelectFundingSource = @"
                SELECT ID, [Name] FROM FundingSource";
        #endregion

        #region methods
        /// <summary>
        /// Gets the service max id.
        /// </summary>
        /// <returns></returns>
        public int SelectServiceMaxId()
        {
            return int.Parse(SqlHelper.ExecuteScalar(_sqlConnection, CommandType.Text, QueryStringGetMaxId).ToString());
        }

        /// <summary>
        /// Inserts the funder.
        /// </summary>
        /// <param name="funder">The funder.</param>
        /// <returns></returns>
        public int InsertFunder(Funder funder)
        {
            try
            {
                var param = new SqlParameter[20];
                param[0] = new SqlParameter("@FundingSource", SqlDbType.NVarChar, 100){Value = funder.FundingSource};
                param[1] = new SqlParameter("@ContactID", SqlDbType.Int){Value = funder.ContactId};
                param[2] = new SqlParameter("@FundingAmount", SqlDbType.Float){Value = funder.FundingAmount};
                param[3] = new SqlParameter("@FundingNeeds", SqlDbType.NVarChar){Value = funder.FundingNeeds};
                param[4] = new SqlParameter("@FundingContinuationNeed", SqlDbType.Bit) { Value = funder.FundingContinuationNeed };
                param[5] = new SqlParameter("@FundingStart", SqlDbType.VarChar) { Value = funder.FundingStart };
                param[6] = new SqlParameter("@FundingEnd", SqlDbType.VarChar) { Value = funder.FundingEnd };
                param[7] = new SqlParameter("@FundingCotinuationAmount", SqlDbType.Float) { Value = funder.FundingCotinuationAmount };
                param[8] = new SqlParameter("@FundingContinuationDetail", SqlDbType.Text) { Value = funder.FundingContinuationDetail };
                param[9] = new SqlParameter("@FundrasingForText", SqlDbType.Text) { Value = funder.FundrasingForText };
                param[10] = new SqlParameter("@FundrasingNeeds", SqlDbType.NVarChar) { Value = funder.FundrasingNeeds };
                param[11] = new SqlParameter("@FundrasingWhy", SqlDbType.NVarChar) { Value = funder.FundrasingWhy };
                param[12] = new SqlParameter("@FundrasingComplete", SqlDbType.Bit) { Value = funder.FundrasingComplete };
                param[13] = new SqlParameter("@FundrasingRequireBy", SqlDbType.VarChar) { Value = funder.FundrasingRequireBy };
                param[14] = new SqlParameter("@FundrasingDonorAnonymous", SqlDbType.Bit) { Value = funder.FundrasingDonorAnonymous };
                param[15] = new SqlParameter("@FundrasingDonorAmount", SqlDbType.VarChar) { Value = funder.FundrasingDonorAmount };
                param[16] = new SqlParameter("@FundrasingDonationDate", SqlDbType.VarChar) { Value = funder.FundrasingDonationDate };
                param[17] = new SqlParameter("@FundrasingDonationIncremental", SqlDbType.Bit) { Value = funder.FundrasingDonationIncremental };
                param[18] = new SqlParameter("@FundrasingCompleteDate", SqlDbType.VarChar) { Value = funder.FundrasingCompleteDate };
                param[19] = new SqlParameter("@ServiceID", SqlDbType.Int) { Value = funder.ServiceId };
                return SqlHelper.ExecuteNonQuery(_sqlConnection, CommandType.Text, QueryStringInsertFunder, param);
            }
            catch(SqlException e)
            {
                ExceptionHandler.WriteErrorLog(e);
                return -1;
            }
        }

        /// <summary>
        /// Updates the funder.
        /// </summary>
        /// <param name="funder">The funder.</param>
        /// <returns></returns>
        public int UpdateFunder(Funder funder)
        {
            try
            {
                var param = new SqlParameter[20];
                param[0] = new SqlParameter("@FundingSource", SqlDbType.NVarChar, 100) { Value = funder.FundingSource };
                param[1] = new SqlParameter("@ContactID", SqlDbType.Int) { Value = funder.ContactId };
                param[2] = new SqlParameter("@FundingAmount", SqlDbType.Float) { Value = funder.FundingAmount };
                param[3] = new SqlParameter("@FundingNeeds", SqlDbType.NVarChar) { Value = funder.FundingNeeds };
                param[4] = new SqlParameter("@FundingContinuationNeed", SqlDbType.Bit) { Value = funder.FundingContinuationNeed };
                param[5] = new SqlParameter("@FundingStart", SqlDbType.VarChar) { Value = funder.FundingStart };
                param[6] = new SqlParameter("@FundingEnd", SqlDbType.VarChar) { Value = funder.FundingEnd };
                param[7] = new SqlParameter("@FundingCotinuationAmount", SqlDbType.Float) { Value = funder.FundingCotinuationAmount };
                param[8] = new SqlParameter("@FundingContinuationDetail", SqlDbType.Text) { Value = funder.FundingContinuationDetail };
                param[9] = new SqlParameter("@FundrasingForText", SqlDbType.Text) { Value = funder.FundrasingForText };
                param[10] = new SqlParameter("@FundrasingNeeds", SqlDbType.NVarChar) { Value = funder.FundrasingNeeds };
                param[11] = new SqlParameter("@FundrasingWhy", SqlDbType.NVarChar) { Value = funder.FundrasingWhy };
                param[12] = new SqlParameter("@FundrasingComplete", SqlDbType.Bit) { Value = funder.FundrasingComplete };
                param[13] = new SqlParameter("@FundrasingRequireBy", SqlDbType.VarChar) { Value = funder.FundrasingRequireBy };
                param[14] = new SqlParameter("@FundrasingDonorAnonymous", SqlDbType.Bit) { Value = funder.FundrasingDonorAnonymous };
                param[15] = new SqlParameter("@FundrasingDonorAmount", SqlDbType.VarChar) { Value = funder.FundrasingDonorAmount };
                param[16] = new SqlParameter("@FundrasingDonationDate", SqlDbType.VarChar) { Value = funder.FundrasingDonationDate };
                param[17] = new SqlParameter("@FundrasingDonationIncremental", SqlDbType.Bit) { Value = funder.FundrasingDonationIncremental };
                param[18] = new SqlParameter("@FundrasingCompleteDate", SqlDbType.VarChar) { Value = funder.FundrasingCompleteDate };
                param[19] = new SqlParameter("@ServiceID", SqlDbType.Int) { Value = funder.ServiceId };
                return SqlHelper.ExecuteNonQuery(_sqlConnection, CommandType.Text, QueryStringUpdateFunder, param);
            }
            catch (SqlException e)
            {
                ExceptionHandler.WriteErrorLog(e);
                return -1;
            }
        }

        /// <summary>
        /// Selects the funder by id.
        /// </summary>
        /// <param name="id">The id.</param>
        /// <returns></returns>
        public Funder SelectFunderById(int id)
        {
            var param = new SqlParameter("@id", SqlDbType.Int) {Value = id};
            var dataTable = SqlHelper.ExecuteDataSet(_sqlConnection, CommandType.Text, QueryStringSelectFunderById, param).Tables[0];
            if(dataTable.Rows.Count>0)
            {
                var dataRow = dataTable.Rows[0];
                return new Funder
                {
                    FundingSource = (string)dataRow["FundingSource"],
                    ContactId = (int)dataRow["ContactID"],
                    FundingAmount = float.Parse(dataRow["FundingAmount"].ToString()),
                    FundingNeeds = (string)dataRow["FundingNeeds"],
                    FundingContinuationNeed = (bool)dataRow["FundingContinuationNeed"],
                    FundingStart = (string)dataRow["FundingStart"],
                    FundingEnd = (string)dataRow["FundingEnd"],
                    FundingCotinuationAmount = float.Parse(dataRow["FundingCotinuationAmount"].ToString()),
                    FundingContinuationDetail = (string)dataRow["FundingContinuationDetail"],
                    FundrasingForText = (string)dataRow["FundrasingForText"],
                    FundrasingNeeds = (string)dataRow["FundrasingNeeds"],
                    FundrasingWhy = (string)dataRow["FundrasingWhy"],
                    FundrasingComplete = (bool)dataRow["FundrasingComplete"],
                    FundrasingCompleteDate = (string)dataRow["FundrasingCompleteDate"],
                    FundrasingRequireBy = (string)dataRow["FundrasingRequireBy"],
                    FundrasingDonorAnonymous = (bool)dataRow["FundrasingDonorAnonymous"],
                    FundrasingDonorAmount = dataRow["FundrasingDonorAmount"].ToString(),
                    FundrasingDonationDate = (string)dataRow["FundrasingDonationDate"],
                    FundrasingDonationIncremental = (bool)dataRow["FundrasingDonationIncremental"],
                    ServiceId = (int)dataRow["ServiceID"]
                };
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// Selects the funding source.
        /// </summary>
        /// <returns></returns>
        public DataTable SelectFundingSource()
        {
            var dataTable = SqlHelper.ExecuteDataSet(_sqlConnection, CommandType.Text, QueryStringSelectFundingSource);
            return dataTable.Tables[0];
        }
        #endregion
    }
}
